package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Car;
import com.xqgl.dao.CarDao;
import com.xqgl.util.ConnertionUtil;

public class CarDaoImp implements CarDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Car> getAllUserInfos() {
		ArrayList<Car> cars = new ArrayList<>();
		String sql = "select * from car ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Car car = new Car();
				car.setId(rst.getInt("id"));
				car.setCarpicture(rst.getString("carpicture"));
				car.setUsername(rst.getString("username"));
				car.setCarcolour(rst.getString("carcolour"));
				car.setCarnum(rst.getString("carnum"));
				car.setCarremark(rst.getString("carremark"));
				car.setCarctime(rst.getString("carctime"));
				cars.add(car);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cars;
	}

	@Override
	public int add(Car car) throws SQLException {
		String sql = "insert into car(carpicture,username,carcolour,carnum,carremark,carctime)"
				+ "values(?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, car.getCarpicture());
		ps.setString(2, car.getUsername());
		ps.setString(3, car.getCarcolour());
		ps.setString(4, car.getCarnum());
		ps.setString(5, car.getCarremark());
		ps.setString(6, car.getCarctime());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Car car) throws SQLException {
		String sql = "update car set carpicture = '"+car.getCarpicture()+"', username = '"+car.getUsername()+"', carcolour = '"+car.getCarcolour()+"', "
				+ "carnum = '"+car.getCarnum()+"', carremark = '"+car.getCarremark()+"', carctime = '"+car.getCarctime()+"' where id = "+car.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from car where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Car findById(int id) throws SQLException {
		Car car = new Car();
		String sql = "select * from car where id = ?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		while(set.next()){
			car.setId(set.getInt("id"));
			car.setCarpicture(set.getString("carpicture"));
			car.setUsername(set.getString("username"));
			car.setCarcolour(set.getString("carcolour"));
			car.setCarnum(set.getString("carnum"));
			car.setCarremark(set.getString("carremark"));
			car.setCarctime(set.getString("carctime"));
		}
		ps.close();
		return car;
	}
}
